﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Data.OracleClient;
using BoShii.FrameWork;

namespace Boshii.Map.DAL.Common
{
    /// <summary>
    /// DalBase基类
    /// </summary>
    public class DalBase : IDisposable
    {
        #region members
        /// <summary>
        /// DbOwner
        /// </summary>
        protected string dbOwner = null;

        /// <summary>
        /// oracHelper
        /// </summary>
        protected OracleHelper oracHelper = null;
        #endregion

        #region	 DacBase
        /// <summary>
        /// DacBase
        /// </summary>
        /// <param name="dbOwner">dbOwner</param>
        public DalBase(string dbOwner)
        {
            if (dbOwner == null || dbOwner == string.Empty)
            {
                this.dbOwner = "dbo";
                return;
            }
            this.dbOwner = dbOwner;
            this.oracHelper = new OracleHelper();
        }
        #endregion

        #region	 DacBase
        /// <summary>
        /// DacBase
        /// </summary>
        /// <remarks>
        /// </remarks>
        public DalBase()
        {
            this.dbOwner = XmlSettings.GetWebXmlSettingString("DBOwner");
            this.oracHelper = new OracleHelper();
        }
        #endregion

        #region Dispose
        /// <summary>
        /// Dispose
        /// </summary>
        public void Dispose()
        {
            GC.SuppressFinalize(this);
            Dispose(true);
        }

        /// <summary>
        /// Dispose
        /// </summary>
        /// <param name="disposing">Dispose</param>
        protected virtual void Dispose(bool disposing)
        {
            lock (this)
            {
                if (disposing)
                {
                    if (null != oracHelper)
                    {
                        oracHelper.Dispose();
                    }
                    oracHelper = null;
                }
            }
        }

        /// <summary>
        /// ~DacBase
        /// </summary>
        ~DalBase()
        {
            Dispose(false);
        }
        #endregion

        #region	+
        #region	RemoveDeletedRows
        /// <summary>
        /// RemoveDeletedRows
        /// </summary>
        /// <param name="ds">DataSet</param>
        public virtual void RemoveDeletedRows(DataSet ds)
        {
            foreach (DataTable table in ds.Tables)
            {
                RemoveDeletedRows(table);
            }
        }
        #endregion

        #region	RemoveDeletedRows
        /// <summary>
        /// RemoveDeletedRows
        /// </summary>
        /// <param name="table">table</param>
        public virtual void RemoveDeletedRows(DataTable table)
        {
            if (table.Columns.Contains("Disabled"))
            {
                foreach (DataRow row in table.Rows)
                {
                    if ((bool)row["Disabled"] == true)
                    {
                        row.Delete();
                    }
                }
            }
            table.AcceptChanges();
        }
        #endregion
        #endregion

        #region static GetServerDateTime
        /// <summary>
        /// GetServerDateTime
        /// </summary>
        /// <returns></returns>
        static public DateTime GetServerDateTime()
        {
            DateTime serverDate;

            using (OracleHelper oracHelper = new OracleHelper())
            using (OracleCommand c = new OracleCommand("select getdate()"))
            {
                oracHelper.Open();
                serverDate = (DateTime)oracHelper.ExecuteScalar(c);
            }
            return serverDate;
        }
        #endregion

        #region ++   Protected Method
        #region +    GetAllTableRows
        /// <summary>
        /// GetAllTableRows
        /// </summary>
        /// <param name="ds">ds</param>
        /// <param name="includeDeletedRows">includeDeletedRows</param>
        protected virtual void GetAllTableRows(
            DataSet ds,
            bool includeDeletedRows
            )
        {
            foreach (DataTable table in ds.Tables)
            {
                this.GetAllRows(table, null, null, includeDeletedRows);
            }
        }
        #region GetAllTableRows
        /// <summary>
        /// GetAllTableRows
        /// </summary>
        /// <param name="ds">ds</param>
        protected virtual void GetAllTableRows(
            DataSet ds
            )
        {
            GetAllTableRows(ds, false);
        }
        #endregion
        #endregion

        #region +    GetAllRows
        /// <summary>
        /// GetAllRows
        /// </summary>
        /// <param name="table">table</param>
        /// <param name="whereString">whereString</param>
        /// <param name="whererParams">whererParams/param>
        /// <param name="includeDeletedRows">includeDeletedRows</param>
        /// <param name="desels">desels</param>
        protected virtual void GetAllRows(
            DataTable table,
            string whereString,
            List<IWebOracleParameter> whererParams,
            bool includeDeletedRows,
            params string[] desels
            )
        {
            string fieldNames = this.CreateFieldNames(table, "A", desels);

            string sql = string.Format(
                "select {2} from {0}.{1} A",
                this.dbOwner,
                table.TableName,
                fieldNames
                );

            if (!includeDeletedRows)
            {
                sql += " where Disabled=0"
                    + ((whereString != null && whereString != string.Empty) ? (" and " + whereString) : string.Empty);
            }
            else
            {
                sql += ((whereString != null && whereString != string.Empty) ? (" where " + whereString) : string.Empty);
            }

            using (OracleCommand command = new OracleCommand(sql))
            {
                if (whererParams != null)
                {
                    foreach (IWebOracleParameter param in whererParams)
                    {
                        command.Parameters.Add(param.Parameter);
                    }
                }
                this.oracHelper.Execute(command, table);
            }
        }
        #region overload
        /// <summary>
        /// GetAllRows
        /// </summary>
        /// <param name="table">table</param>
        protected virtual void GetAllRows(
            DataTable table
            )
        {
            this.GetAllRows(table, null, null, false);
        }
        #endregion

        #region overload
        protected virtual void GetAllRows(DataTable table, bool inculdeDeleteRows)
        {
            this.GetAllRows(table, null, null, inculdeDeleteRows);
        }
        #endregion

        #region overload
        /// <summary>
        /// GetAllRows
        /// </summary>
        /// <param name="table">table</param>
        /// <param name="whereString">whereString</param>
        /// <param name="whererParams">whererParams</param>
        protected virtual void GetAllRows(
            DataTable table,
            string whereString,
            List<IWebOracleParameter> whererParams
            )
        {
            this.GetAllRows(table, whereString, whererParams, false);
        }
        #endregion
        #region overload
        /// <summary>
        /// GetAllRows
        /// </summary>
        /// <param name="table">table</param>
        /// <param name="includeDeletedRows">includeDeletedRows</param>
        /// <param name="desels">desels</param>
        protected virtual void GetAllRows(
            DataTable table,
            bool includeDeletedRows,
            params string[] desels
            )
        {
            this.GetAllRows(table, null, null, includeDeletedRows, desels);
        }
        #endregion
        #endregion

        #region +    GetAllRowsAppendedDisabled
        /// <summary>
        /// GetAllRowsAppendedDisabled
        /// </summary>
        /// <param name="table">table</param>
        /// <param name="whereString">whereString</param>
        /// <param name="whererParams">whererParams</param>
        /// <param name="desels">desels</param>
        protected virtual void GetAllRowsAppendedDisabled(
            DataTable table,
            string whereString,
            List<IWebOracleParameter> whererParams,
            params string[] desels
            )
        {
            string[] newDesels = new string[desels.Length + 1];
            for (int i = 0; i < desels.Length; ++i)
            {
                newDesels[i] = desels[i];
            }
            newDesels[newDesels.Length - 1] = "Disabled";

            string fieldNames = this.CreateFieldNames(table, "A", newDesels);

            string sql = string.Format(
                "select {2}, 0 as Disabled from {0}.{1} A",
                this.dbOwner,
                table.TableName,
                fieldNames
                );

            sql += ((whereString != null && whereString != string.Empty) ? (" where " + whereString) : string.Empty);

            using (OracleCommand command = new OracleCommand(sql))
            {
                foreach (IWebOracleParameter param in whererParams)
                {
                    command.Parameters.Add(param.Parameter);
                }

                this.oracHelper.Execute(command, table);
            }

            table.Columns["Disabled"].ReadOnly = false;
            table.Columns["Disabled"].DefaultValue = false;
        }
        #region overload
        /// <summary>
        /// GetAllRowsAppendedDisabled
        /// </summary>
        /// <param name="table">table</param>
        protected virtual void GetAllRowsAppendedDisabled(
            DataTable table
            )
        {
            this.GetAllRowsAppendedDisabled(table, null, (List<IWebOracleParameter>)null);
        }
        #endregion
        #region overload
        /// <summary>
        /// GetAllRowsAppendedDisabled
        /// </summary>
        /// <param name="table">table</param>
        /// <param name="desels">desels</param>
        protected virtual void GetAllRowsAppendedDisabled(
            DataTable table,
            params string[] desels
            )
        {
            this.GetAllRowsAppendedDisabled(table, null, null, desels);
        }
        #endregion
        #endregion

        #region CreateFieldNames
        /// <summary>
        /// CreateFieldNames
        /// </summary>
        /// <param name="table">table</param>
        /// <param name="tableAlias">tableAlias</param>
        /// <param name="desels">desels</param>
        /// <returns></returns>
        protected virtual string CreateFieldNames(
            DataTable table,
            string tableAlias,
            params string[] desels
            )
        {
            string fieldNames = string.Empty;

            foreach (DataColumn col in table.Columns)
            {
                if (0 != desels.Length)
                {
                    bool isDesels = false;
                    for (int i = 0; i < desels.Length; ++i)
                    {
                        if (true == col.Caption.Equals(desels[i]))
                        {
                            isDesels = true;
                            break;
                        }
                    }
                    if (true == isDesels)
                    {
                        continue;
                    }
                }
                if (tableAlias == null || tableAlias == string.Empty)
                {
                    fieldNames += string.Format(
                        "{0} {1}.{2}.{3}",
                        ((0 == fieldNames.Length) ? string.Empty : ","),
                        this.dbOwner,
                        table.TableName,
                        col.Caption);
                }
                else
                {
                    fieldNames += string.Format(
                        "{0} {1}.{2}",
                        ((0 == fieldNames.Length) ? string.Empty : ","),
                        tableAlias,
                        col.Caption);
                }
            }

            return fieldNames;
        }
        #endregion
        #endregion

        #region ExistsValue
        /// <summary>
        /// 检测表中指定字段是否存在相同值
        /// </summary>
        /// <param name="tableName"></param>
        /// <param name="fieldsName"></param>
        /// <param name="filedValue"></param>
        /// <returns></returns>
        public bool ExistSameVlue(string tableName, string fieldName, string fieldValue)
        {
            bool hasSameValue = false;
            string[][] inParam = new string[4][];
            inParam[0] = new string[] { "p_TableName", "p_FieldsName", "p_FieldsValue" };
            inParam[1] = new string[] { "varchar2", "varchar2", "varchar2" };
            inParam[2] = new string[] { "500", "500", "500" };
            inParam[3] = new string[] { tableName, fieldName, fieldValue.ToLower() };

            string[][] outParam = new string[4][];
            outParam[0] = new string[] { "p_hasSameValue" };
            outParam[1] = new string[] { "int" };
            outParam[2] = new string[] { "1" };
            outParam[3] = new string[] { "0" };

            using (OracleHelper orahp = new OracleHelper())
            {
                orahp.Open();
                orahp.ExecuteSP(string.Format("{0}.PROC_EXIST_SAMEVALUE", dbOwner), inParam, ref outParam);
            }
            int returnValue = 0;
            Int32.TryParse(outParam[3][0], out returnValue);
            hasSameValue = returnValue.Equals(1);
            return hasSameValue;
        }

        public bool ExistSameVlue_1(string tableName, string fieldName, string fieldValue, string pkFieldName, string pkFiedlValue)
        {
            bool hasSameValue = false;
            string[][] inParam = new string[4][];
            inParam[0] = new string[] { "p_TableName", "p_FieldName", "p_FieldValue", "p_PkFieldName", "p_PkFieldValue" };
            inParam[1] = new string[] { "varchar2", "varchar2", "varchar2", "varchar2", "varchar2" };
            inParam[2] = new string[] { "500", "150", "150", "150", "150" };
            inParam[3] = new string[] { tableName, fieldName, fieldValue.ToLower(), pkFieldName, pkFiedlValue };

            string[][] outParam = new string[4][];
            outParam[0] = new string[] { "p_hasSameValue" };
            outParam[1] = new string[] { "int" };
            outParam[2] = new string[] { "1" };
            outParam[3] = new string[] { "0" };

            using (OracleHelper orahp = new OracleHelper())
            {
                orahp.Open();
                orahp.ExecuteSP(string.Format("{0}.PROC_EXIST_SAMEVALUE_1", dbOwner), inParam, ref outParam);
            }
            int returnValue = 0;
            Int32.TryParse(outParam[3][0], out returnValue);
            hasSameValue = returnValue.Equals(1);
            return hasSameValue;
        }
        #endregion
    }
}
